﻿using ET.DBCommon.Model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ET.DBCommon.DAL
{
    public static class VoiceLibraryDAL
    {
        public static List<VoiceLibraryModel> GetVoiceLibrary()
        {
            List<VoiceLibraryModel> voiceLibraryModelList = new List<VoiceLibraryModel>();
            string _mySqlText = "select * from voicelibrary";
            DataSet ds = new DataSet();
            if (ds.Tables.Count > 0)
            {
                ds = DbHelper.ExecuteDataset(_mySqlText);
                DataTable myTable = ds.Tables[0];
                if (myTable != null && myTable.Rows.Count > 0)
                {
                    for (int i = 0; i < myTable.Rows.Count; i++)
                    {
                        var MyVoiceModel = myTable.Rows[i];
                        VoiceLibraryModel voiceLibraryModel = new VoiceLibraryModel();
                        voiceLibraryModel.ID = Convert.ToInt32(MyVoiceModel["ID"].ToString());
                        voiceLibraryModel.CommandType = MyVoiceModel["CommandType"].ToString();
                        voiceLibraryModel.CommandDescr = MyVoiceModel["CommandDescr"].ToString();
                        voiceLibraryModel.DevType = Convert.ToInt32(MyVoiceModel["DevType"].ToString());
                        voiceLibraryModel.DevTypeName = MyVoiceModel["DevTypeName"].ToString();
                        voiceLibraryModel.DevTypeBranch = MyVoiceModel["DevTypeBranch"].ToString();
                        voiceLibraryModel.TalkContent = MyVoiceModel["TalkContent"].ToString();
                        voiceLibraryModel.KeyWords = MyVoiceModel["KeyWords"].ToString();
                        voiceLibraryModel.ReplyContent = MyVoiceModel["ReplyContent"].ToString();
                        voiceLibraryModel.CommandStep = MyVoiceModel["CommandStep"].ToString();
                        voiceLibraryModel.LimitMatch = Convert.ToInt32(MyVoiceModel["LimitMatch"].ToString());
                        voiceLibraryModel.Parameter = MyVoiceModel["Parameter"].ToString();
                        voiceLibraryModelList.Add(voiceLibraryModel);
                    }
                }
            }
            return voiceLibraryModelList;
        }
        public static List<VoiceLibraryModel> GetVoiceLibrary( int TestQuestionID)
        {
            List<VoiceLibraryModel> voiceLibraryModelList = new List<VoiceLibraryModel>();
            string _mySqlText = "select * from  voicelibrary a inner join testvoicelibrary b on a.id=b.VoiceLibraryID"+ " where b.TestQuestionID="+ TestQuestionID;
            DataSet ds = new DataSet();
            ds = DbHelper.ExecuteDataset(_mySqlText);
            DataTable myTable = ds.Tables[0];
            if (myTable != null && myTable.Rows.Count > 0)
            {
                for (int i = 0; i < myTable.Rows.Count; i++)
                {
                    var MyVoiceModel = myTable.Rows[i];
                    VoiceLibraryModel voiceLibraryModel = new VoiceLibraryModel();
                    voiceLibraryModel.ID = Convert.ToInt32(MyVoiceModel["ID"].ToString());
                    voiceLibraryModel.CommandType = MyVoiceModel["CommandType"].ToString();
                    voiceLibraryModel.CommandDescr = MyVoiceModel["CommandDescr"].ToString();
                    voiceLibraryModel.DevType = Convert.ToInt32(MyVoiceModel["DevType"].ToString());
                    voiceLibraryModel.DevTypeName = MyVoiceModel["DevTypeName"].ToString();
                    voiceLibraryModel.DevTypeBranch = MyVoiceModel["DevTypeBranch"].ToString();
                    voiceLibraryModel.TalkContent = MyVoiceModel["TalkContent"].ToString();
                    voiceLibraryModel.KeyWords = MyVoiceModel["KeyWords"].ToString();
                    voiceLibraryModel.ReplyContent = MyVoiceModel["ReplyContent"].ToString();
                    voiceLibraryModel.CommandStep = MyVoiceModel["CommandStep"].ToString();
                    voiceLibraryModel.LimitMatch = Convert.ToInt32(MyVoiceModel["LimitMatch"].ToString());
                    voiceLibraryModel.Parameter = MyVoiceModel["Parameter"].ToString();
                    voiceLibraryModelList.Add(voiceLibraryModel);
                }
            }
            return voiceLibraryModelList;
        }
        public static bool InsertVoiceLibrary(out int newId, VoiceLibraryModel model)
        {
            string sql = @"INSERT INTO `voicelibrary`
                            (`CommandType`,
                            `CommandDescr`,
                            `DevType`,
                            `DevTypeName`,
                            `DevTypeBranch`,
                            `TalkContent`,
                            `KeyWords`,
                            `ReplyContent`,
                            `CommandStep`,
                            `LimitMatch`,
                            `Parameter`)                          
                            VALUES(@CommandType,@CommandDescr,@DevType,@DevTypeName,@DevTypeBranch,@TalkContent,@KeyWords,
                            @ReplyContent,@CommandStep,@LimitMatch,@Parameter)";
            MySqlParameter[] parms = {
                new MySqlParameter("@CommandType", model.CommandType ),
                new MySqlParameter("@CommandDescr", model.CommandDescr),
                new MySqlParameter("@DevType", model.DevType),
                new MySqlParameter("@DevTypeName", model.DevTypeName),

                new MySqlParameter("@DevTypeBranch", model.DevTypeBranch ),
                new MySqlParameter("@TalkContent", model.TalkContent),
                new MySqlParameter("@KeyWords", model.KeyWords),
                new MySqlParameter("@ReplyContent", model.ReplyContent),
                new MySqlParameter("@CommandStep", model.CommandStep ),
                new MySqlParameter("@LimitMatch", model.LimitMatch ),
                new MySqlParameter("@Parameter", model.Parameter),               
            };

            return DbHelper.ExecuteNonQuery(out newId, sql, parms) > 0;
        }

        public static bool UpdateVoiceLibrary(VoiceLibraryModel model)
        {
            string sql = @"UPDATE `voicelibrary`
                            SET
                            `CommandType` = @CommandType,
                            `CommandDescr` = @CommandDescr,
                            `DevType` = @DevType,
                            `DevTypeName` = @DevTypeName,
                            `DevTypeBranch` = @DevTypeBranch,
                            `TalkContent` = @TalkContent,
                            `KeyWords` = @KeyWords,
                            `ReplyContent` = @ReplyContent,
                            `CommandStep` = @CommandStep,
                            `LimitMatch` = @LimitMatch,
                            `Parameter` = @Parameter
                            WHERE `ID` = @ID ";

            MySqlParameter[] parms = {
                new MySqlParameter("@ID", model.ID),
                new MySqlParameter("@CommandType", model.CommandType ),
                new MySqlParameter("@CommandDescr", model.CommandDescr),
                new MySqlParameter("@DevType", model.DevType),
                new MySqlParameter("@DevTypeName", model.DevTypeName),

                new MySqlParameter("@DevTypeBranch", model.DevTypeBranch ),
                new MySqlParameter("@TalkContent", model.TalkContent),
                new MySqlParameter("@KeyWords", model.KeyWords),
                new MySqlParameter("@ReplyContent", model.ReplyContent),
                new MySqlParameter("@CommandStep", model.CommandStep ),
                new MySqlParameter("@LimitMatch", model.LimitMatch ),
                new MySqlParameter("@Parameter", model.Parameter),
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }
        public static bool DeleteVoiceLibrary(int ID)
        {
            string sql = "delete from voicelibrary WHERE ID = @ID";
            MySqlParameter[] parms = {
                new MySqlParameter("@ID", ID)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

    }
}
